package TruongNhaLib;

import java.util.ArrayList;
import java.util.HashMap;

import encrypt.AesCrypto;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
public class DatabaseHandler extends SQLiteOpenHelper {
 
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "truongnha";
 
    // Table name
    private static final String TABLE_LOGIN = "login";
    private static final String TABLE_CLASS = "classes";
    private static final String TIME_CLASS = "time";
    private static final String TABLE_REMEMBER = "remember";
    
    
    // Remember table
    private static final String KEY_USERNAME ="username";
    private static final String KEY_PASSWORD ="password";
    
    // Login Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_LAST_NAME = "lastname";
    private static final String KEY_FIRST_NAME = "firstname";
    private static final String KEY_TYPE = "type";
    private static final String KEY_CLASS = "class";
    private static final String KEY_SCHOOL = "school";
    private static final String KEY_BIRTH  = "birth"; 
    private static final String KEY_USER_ID  = "user_id"; 
    //Classes table columns names: only teacher user.
    private static final String KEY_CLS_ID = "cls_id";
    private static final String KEY_CLASS_ID = "class_id";
    private static final String KEY_CLS_NAME = "cls_name";
    private static final String KEY_CLS_SUBJECT = "cls_subject";
    private static final String KEY_CLS_SIZE = "cls_size";
    private static final String KEY_CLS_MAINTEACHER = "cls_mainteacher";
    private static final String KEY_CLS_SUBJECT_ID = "cls_subject_id";
    
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
    	//Create remember table:
    	String CREATE_REMEMBER_TABLE = "CREATE TABLE " + TABLE_REMEMBER + "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                + KEY_USERNAME + " TEXT,"
                + KEY_PASSWORD + " TEXT,"
                + KEY_TYPE + " TEXT"+ ")";
        db.execSQL(CREATE_REMEMBER_TABLE);
        Log.d("LOG", "On create REMEMBER tables");
        //Create login table:
    	String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                + KEY_LAST_NAME + " TEXT,"
                + KEY_FIRST_NAME + " TEXT,"
                + KEY_TYPE + " TEXT,"
                + KEY_CLASS + " TEXT,"
                + KEY_SCHOOL + " TEXT,"
                + KEY_BIRTH + " TEXT," 
    			+ KEY_USER_ID + " TEXT"+ ")";

        db.execSQL(CREATE_LOGIN_TABLE);
        Log.d("LOG", "On create LOGIN tables");
        //Create class table:
        String CREATE_CLASS_TABLE = "CREATE TABLE " + TABLE_CLASS + "("
        		+ KEY_CLS_ID + " INTEGER PRIMARY KEY,"
        		+ KEY_CLASS_ID + " TEXT,"
                + KEY_CLS_NAME + " TEXT,"
                + KEY_CLS_SUBJECT + " TEXT,"
                + KEY_CLS_SIZE + " TEXT,"
                + KEY_CLS_MAINTEACHER + " TEXT,"
                + KEY_CLS_SUBJECT_ID + " INTEGER"
                + ")";
        db.execSQL(CREATE_CLASS_TABLE);
        Log.d("LOG", "On create CLASS tables");
    }
 
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older tables if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CLASS); 
        db.execSQL("DROP TABLE IF EXISTS " + TIME_CLASS); 
        // Create tables again
        onCreate(db);
        Log.d("LOG", "On Drop 2 tables");
    }
 

//get user type:    
    public String userType( ) {
        String selectQuery = "SELECT  type FROM " + TABLE_LOGIN;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        String type = cursor.getString(0);
        cursor.close();
    	db.close(); // Closing database connection
    	Log.d("userType: ", type);
    	
    	return type;
    }
    
    /**
     * Storing remember username and password in database
     * */
    public void addRemember(String username, String password, String type) {
    	Log.d("addRemember", "Post 8.8  add remember " +username+"||"+password + "||"+type);
    	SQLiteDatabase db = this.getWritableDatabase();
        //del previous
    	//db.delete(TABLE_REMEMBER, null, null);
        //encrypt data
    	AesCrypto aes = new AesCrypto();
    	String encodedUsername = aes.encrypt(username);
    	String encodedPassword = aes.encrypt(password);
        ContentValues values = new ContentValues();
        values.put(KEY_USERNAME, encodedUsername); // username
        values.put(KEY_PASSWORD, encodedPassword); // password
        values.put(KEY_TYPE, type);			// type
        // Inserting Row
        db.insert(TABLE_REMEMBER, null, values);
        Log.d("addrememberok", "");
        db.close(); // Closing database connection
    }
    //Get remember username & password
    public String[] getRemember(){
    	AesCrypto aes = new AesCrypto();
        String selectQuery = "SELECT  * FROM " + TABLE_REMEMBER;
        String[] remember = {"","",""};
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        while(cursor.isAfterLast() == false){
        	Log.d("getRemember", "username: "+cursor.getString(1)+"|password: "+cursor.getString(2) );
        	remember[0]=aes.decrypt(cursor.getString(1));
        	remember[1]=aes.decrypt(cursor.getString(2));
        	remember[2]=cursor.getString(3);
        	cursor.moveToNext();
        }//end while.
        cursor.close();
        db.close();    	
    	return remember;
    }//End getTime().

    /**
     * Storing user details in database
     * */
    public void addUser(String lastname, String firstname, String type, String classclass, String school, String birth, String user_id) {
    	Log.d("LOG", "Post 8.8  add user " +lastname+firstname + type);
    	SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_LAST_NAME, lastname); // Name
        values.put(KEY_FIRST_NAME, firstname); // Name
        values.put(KEY_TYPE, type); // type
        values.put(KEY_CLASS, classclass); 
        values.put(KEY_SCHOOL, school); 
        values.put(KEY_BIRTH, birth);  
        values.put(KEY_USER_ID, user_id);
        // Inserting Row
        db.insert(TABLE_LOGIN, null, values);
        db.close(); // Closing database connection
    }
    // add class of teacher.
    public void addClass(String classId, String name, String subject, String size, String mainteacher, int subjectId) {
    	Log.d("LOG", "Post 8.8  add class " +name + subject);
    	SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_CLASS_ID, classId); // Name
        values.put(KEY_CLS_NAME, name); // Name
        values.put(KEY_CLS_SUBJECT, subject); // subject
        values.put(KEY_CLS_SIZE, size); // size
        values.put(KEY_CLS_MAINTEACHER, mainteacher); // mainteacher
        values.put(KEY_CLS_SUBJECT_ID, subjectId);
        
        // Inserting Row
        db.insert(TABLE_CLASS, null, values);
        db.close(); // Closing database connection
    }    
    /**
     * Getting user data from database
     * */
    public HashMap<String, String> getUserDetails(){
    	Log.d("LOG", "start get userdetail");
    	HashMap<String,String> user = new HashMap<String,String>();
        String selectQuery = "SELECT  * FROM " + TABLE_LOGIN;
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
        	Log.d("LOG", "test: "+cursor.getString(1)+cursor.getString(2)+cursor.getString(3)+cursor.getString(4)+cursor.getString(5)+cursor.getString(6));
        	
        	user.put(KEY_LAST_NAME, cursor.getString(1));
        	user.put(KEY_FIRST_NAME, cursor.getString(2));
            user.put(KEY_TYPE, cursor.getString(3));
            user.put(KEY_CLASS, cursor.getString(4));
            user.put(KEY_SCHOOL, cursor.getString(5));
            user.put(KEY_BIRTH, cursor.getString(6));
            user.put(KEY_USER_ID, cursor.getString(7));
        }
        cursor.close();
        db.close();
        // return user
        Log.d("LOG", "END get userdetail");
        return user;
    }
    //Get Main Class detail
    public ArrayList<HashMap<String, String>> getMainClassDetail(){
    	// Hashmap for ListView
    	ArrayList<HashMap<String, String>> mainClassList = new ArrayList<HashMap<String, String>>();

    	String selectQuery = "SELECT * FROM " + TABLE_CLASS;
    	SQLiteDatabase db = this.getReadableDatabase();
    	Cursor cursor = db.rawQuery(selectQuery, null);
    	cursor.moveToFirst();
    	while (cursor.isAfterLast() == false) {
			if(cursor.getString(3).equals("-1")){// Only main class
		    	HashMap<String, String> mainClass = new HashMap<String, String>();
				mainClass.put("classId", cursor.getString(1));
				mainClass.put("name", cursor.getString(2));
				Log.d("size", cursor.getString(4));
				mainClass.put("size", cursor.getString(4));
				Log.d("subject", cursor.getString(3));
				mainClass.put("subject", "Lớp chủ nhiệm");
				mainClass.put("mainTeacher", cursor.getString(5));
				mainClassList.add(mainClass);
				cursor.close();
				db.close();
				return mainClassList;
			}
			cursor.moveToNext();
		}
    	cursor.close();
    	db.close();
    	return null;
    }
    // Get classes detail:
    public ArrayList<HashMap<String, String>>  getClassDetails(){       
    	// Hashmap for ListView
    	ArrayList<HashMap<String, String>> classList = new ArrayList<HashMap<String, String>>();
 
        String selectQuery = "SELECT  * FROM " + TABLE_CLASS;       
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        while(cursor.isAfterLast() == false){
        	if(!cursor.getString(3).equals("-1")){//all class except main Class
        		HashMap<String,String> classes = new HashMap<String,String>();
	        	Log.d("LOG", "Retrieve: "+cursor.getString(1)+cursor.getString(2)+cursor.getString(3)+cursor.getString(4)+cursor.getString(5));
	        	classes.put("classId", cursor.getString(1));
	        	classes.put("name", cursor.getString(2));
	        	classes.put("subject", cursor.getString(3));
				Log.d("subject", cursor.getString(3));
	        	classes.put("size", cursor.getString(4));
	        	Log.d("size", cursor.getString(4));
	            classes.put("mainteacher", cursor.getString(5));
	            classes.put("subjectId", cursor.getInt(6)+"");
	            classList.add(classes);
        	}
	        cursor.moveToNext();
        }//end while.
        cursor.close();
        db.close();
        return classList;
    }    
    /**
     * Getting user login status
     * return true if rows are there in table
     * */
    public int getRowCount(String table) {
        String countQuery = "SELECT  * FROM " + table;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int rowCount = cursor.getCount();
        db.close();
        cursor.close();
 
        // return row count
        return rowCount;
    }
    /**
     * Re create database
     * Delete all tables and create them again
     * */
    public void resetTables(){
        SQLiteDatabase db = this.getWritableDatabase();
        // Delete All Rows
        db.delete(TABLE_REMEMBER, null, null);
        Log.e("Logout", "del  data of Remember table");
        // Delete All Rows
        db.delete(TABLE_LOGIN, null, null);
        Log.e("Logout", "del  data of Login table");
        db.delete(TABLE_CLASS, null, null);
//        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
        Log.e("Logout", "del  data of Class table");
        db.close();
    }
    /**
     * Re create database
     * Delete all tables except rememberTable
     * */
    public void delData(){
        SQLiteDatabase db = this.getWritableDatabase();
        // Delete All Rows
        db.delete(TABLE_LOGIN, null, null);
        Log.e("DEL", "del  data of Login table");
        db.delete(TABLE_CLASS, null, null);
        Log.e("DEL", "del  data of Class table");

        db.close();
    }
 
}